package com.stps;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

import org.junit.Test;

import com.stps.dao.BaseDao;

//import org.junit.jupiter.api.AfterEach;
//import org.junit.jupiter.api.BeforeEach;
//import org.junit.jupiter.api.Test;

public class JdbcTest {
    static String driverName;//="com.mysql.jdbc.Driver";
    static String url;//="jdbc:mysql://localhost:3306/daodemo?useUnicode=true&characterEncoding=utf-8";
    static String user;//="root";
    static String password;//="123456";
    
    
    
    Connection conn = null;
	private ResultSet rs = null;
	private Statement smt = null;

	static{//静态代码块,在类加载的时候执行
		init();
	}	
	
    //初始化连接参数,从配置文件里获得
	public static void init(){
		Properties properties=new Properties();
		String configFile = "db-daodemo.properties";
		InputStream is=BaseDao.class.getClassLoader().getResourceAsStream(configFile);
		try {
			properties.load(is);
		} catch (IOException e) {
			e.printStackTrace();
		}
		driverName=properties.getProperty("driver");
		url=properties.getProperty("url");
		user=properties.getProperty("user");
		password=properties.getProperty("password");
//		System.out.println("driver:   "+driverName);
//		System.out.println("url:   "+url);
//		System.out.println("user:   "+user);
//		System.out.println("password:   "+password);

	} 
	


    @Test
	public void testStatement() throws Exception {        
		Class.forName(driverName);
        Connection conn= DriverManager.getConnection(url,user,password);
        smt = conn.createStatement();
        String sqlStr = "select * from student;";
        rs = smt.executeQuery(sqlStr);
        while(rs.next()) {
        	System.out.println(rs.getString("sno")+"   "+rs.getString("name")+"   "+rs.getString("sex"));
        }  
        rs.close();
        smt.close();
        conn.close();
	}

    
    
    @Test
    public void testPreparedStatement() throws Exception {        
		Class.forName(driverName);
        Connection conn= DriverManager.getConnection(url,user,password);
        String sqlStr = "select * from student where sno=?;";
        PreparedStatement pstmt = conn.prepareStatement(sqlStr);
        pstmt.setString(1, "1");
        rs = pstmt.executeQuery();
        while(rs.next()) {
        	System.out.println(rs.getString("sno")+"   "+rs.getString("name")+"   "+rs.getString("sex"));
        }
        rs.close();
        pstmt.close();
        conn.close();
	}

    @Test
    public void testInsert() throws Exception {        
		Class.forName(driverName);
        Connection conn= DriverManager.getConnection(url,user,password);
        
        String sqlStr = "insert into student(sno,name,sex) values(?,?,?);";
        
        PreparedStatement pstmt = conn.prepareStatement(sqlStr);
        pstmt.setString(1, "4");
        pstmt.setString(2, "王8");
        pstmt.setString(3, "女");
        
        pstmt.execute();
        
        pstmt.close();
        conn.close();
	}
    
    
    @Test
    public void testDelete() throws Exception {        
		Class.forName(driverName);
        Connection conn= DriverManager.getConnection(url,user,password);
        
        String sqlStr = "delete from student where sno=?;";
        
        PreparedStatement pstmt = conn.prepareStatement(sqlStr);
        pstmt.setString(1, "4");
        pstmt.execute();
        pstmt.close();
        conn.close();
	}
       
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
//    @BeforeEach
//    public void init() {
//    	try {
//			Class.forName(driverName);
//			System.out.println("加载驱动程序 成功  ....");
//		} catch (ClassNotFoundException e) {
//			System.out.println("加载驱动程序失败....");
//		}
//        try {
//			conn= DriverManager.getConnection(url,user,password);
//			System.out.println("数据库连接  成功  ....");
//		} catch (SQLException e) {
//			System.out.println("数据库连接  失败  ....");
//		}
//    	System.out.println("init...................");
//    }
//    
//    @AfterEach
//    public void Destory() {
//        try {
//			rs.close();
//		} catch (SQLException e1) {
//			// TODO Auto-generated catch block
//			e1.printStackTrace();
//		}
//        try {
//			pstmt.close();
//		} catch (SQLException e1) {
//			// TODO Auto-generated catch block
//			e1.printStackTrace();
//		}
//        try {
//			conn.close();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//        System.out.println("Destory..............");
//    } 
}
